/**
 * @author Huynh Ngoc Tin
 *
 */
package uit.tkorg.coauthorgraph.dbaccess;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import uit.tkorg.coauthorgraph.dto.PaperDTO;

public class PaperMapper extends MapperDB {
    public PaperMapper() throws Exception {
        super();
    }

    public PaperMapper(Connection con) {
        super(con);
    }

    /**
     * check if the specified paper exist in the DB
     * @param paperDTO
     * @return
     * @throws Exception
     */
    public boolean isPaperExisted(PaperDTO paperDTO) throws Exception {
        boolean isExist = false;
        try {
            StringBuffer sql = new StringBuffer();
            sql.append(" SELECT * FROM coauthorgraph.paper p");
            sql.append(" WHERE p.title = ? AND p.year = ?");
            Connection con = this.getConnection();
            if (con != null) {
                PreparedStatement stmt = con.prepareStatement(sql.toString());
                stmt.setString(1, paperDTO.getTitle());
                stmt.setInt(2, paperDTO.getYear());
                ResultSet rs = stmt.executeQuery();
                if ((rs != null) && (rs.next())) {
                    isExist = true;
                }
                stmt.close();
            }
        }
        catch (Exception e) {
            System.out.println(e.toString());
            throw e;
        }

        return isExist;
    }

    /**
     * insert a new paper into the DB
     * @param authorDTO
     * @throws Exception
     */
    public int insertNewPaper(PaperDTO paperDTO) throws Exception {
        try {
            StringBuffer sql = new StringBuffer();
            sql.append("INSERT INTO coauthorgraph.paper(doi, url, title, abstract, conferenceID, journalID, year, pages) ");
            sql.append(" VALUES(?, ?, ?, ?, ?, ?, ?, ?)");
            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            stmt.setString(1, paperDTO.getDoi());
            stmt.setString(2, paperDTO.getUrl());
            stmt.setString(3, paperDTO.getTitle());
            stmt.setString(4, paperDTO.getAbstractContent());
            stmt.setInt(5, paperDTO.getConferenceID());
            stmt.setInt(6, paperDTO.getJournalID());
            stmt.setInt(7, paperDTO.getYear());
            stmt.setString(8, paperDTO.getPages());
            stmt.execute();

            // get the last inserted ID
            ResultSet rs = stmt.executeQuery("SELECT LAST_INSERT_ID()");
            int insertPaperID = -1;
            if (rs != null && rs.next())
                insertPaperID = rs.getInt(1);

            stmt.close();
            return insertPaperID;
        }
        catch (Exception e) {
            System.out.println(e.toString());
            throw e;
        }
    }
}
